package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

@Component
public class ScheduleDao extends BaseDao {

    //宝宝课堂管理列表
    public List<Map<String, Object>> getScheduleList() {
        String sql = "SELECT s.id , resource_id ,week_day,img,title,school FROM s_schedule s LEFT JOIN s_resource r ON s.`resource_id` = r.`id` ORDER BY week_day";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("schedule_id", rs.getInt(1));
                row.put("resource_id", rs.getInt(2));
                row.put("week_day", rs.getInt(3));
                row.put("img", rs.getString(4));
                row.put("title", rs.getString(5));
                row.put("school", dicValue(rs.getInt(6)));
                return row;
            }
        });
        return list;
    }

    public Map<String, Object> getSchedule(int id) {
        String sql = "SELECT s.id , resource_id ,week_day,img,title,school FROM s_schedule s LEFT JOIN s_resource r ON s.`resource_id` = r.`id` where s.`id`=?";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("schedule_id", rs.getInt(1));
                row.put("resource_id", rs.getInt(2));
                row.put("week_day", rs.getInt(3));
                row.put("img", rs.getString(4));
                row.put("title", rs.getString(5));
                row.put("school", dicValue(rs.getInt(6)));
                return row;
            }
        }, id);
        return list.size() > 0 ? list.get(0) : null;
    }

    //查字典
    public String dicValue(int id) {
        String sql = "SELECT `value` FROM `w_dic` WHERE id=? LIMIT 0,1";
        List<String> list = this.query(sql, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                String value = rs.getString(1);
                return value;
            }
        }, new Object[]{id});
        return list.size() > 0 ? list.get(0) : null;
    }

    public int delete(int schedule_id) {
        String sql = "delete from s_schedule where id = ?";
        return this.update(sql, schedule_id);
    }

    public int save(int resource_id, int week_day) {
        KeyHolder holder = new GeneratedKeyHolder();
        this.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                String sql = "insert into s_schedule(week_day,resource_id)values(?,?)";
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setInt(1, week_day);
                ps.setInt(2, resource_id);
                return ps;
            }
        }, holder);
        int id = holder.getKey().intValue();
        return id;
    }

    public int update(int schedule_id, int resource_id, int week_day) {
        int rs = this.update("update s_schedule set resource_id=?,week_day=? where id=?",
                resource_id,
                week_day,
                schedule_id);
        return rs;
    }

}
